package com.jse.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Stream;
import java.util.stream.StreamSupport;

import com.jse.Ioc;
import com.jse.Log;
import com.jse.json.Json;
import com.jse.json.JsonObject;

@SuppressWarnings({"rawtypes","unchecked"})
public interface Dao {
	
	static Log log=Log.get(Dao.class);
	
	default Connection getConnection() {
		return Jdbc.connection();
	}

	default <T>List<T> queryForList(String sql,Class<T> clazz,Object...args){
		var list=new ArrayList<T>();
		try(var conn=getConnection();
			var stmt=conn.prepareStatement(sql)){
			if(args!=null) {
				for (int i = 0; i < args.length; i++) {
					stmt.setObject(i+1, args[i]);
				}
			}
			long s1=System.currentTimeMillis();
			if(Jdbc.showsql) {
				log.info("queryForList sql:{} {}ms",stmt.toString().substring(stmt.getClass().getName().length()),(System.currentTimeMillis()-s1));
			}
			var rs=stmt.executeQuery();
			while (rs.next()){
				if(clazz.isPrimitive()||
						CharSequence.class.isAssignableFrom(clazz)) {
					list.add((T) rs.getObject(1));
				}else if(Map.class.isAssignableFrom(clazz)) {
					JsonObject map=new JsonObject(rs);
					list.add((T) map);
				}
			}
			return list;
		}catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return null;
	}
	default Object select(String sql,Object type,Object...args){
		if(type instanceof Class<?> clazz) {
			if(List.class.isAssignableFrom(clazz)) {
				return queryForList(sql,Map.class,args);
			}else if(Map.class.isAssignableFrom(clazz)) {
				return queryForMap(sql,args);
			}else {
				return queryForObject(sql,clazz,args);
			}
		}else if(type instanceof String s) {
			if("list".equals(s)) {
				return queryForList(sql,Map.class,args);
			}else if("map".equals(s)) {
				return queryForMap(sql,args);
			}else if("dict".equals(s)||"kv".equals(s)) {
				Map<String,Object> map=new HashMap<>();
				queryForList(sql,Map.class,args).forEach(m->{
					map.put(m.get("key").toString(),m.get("value"));
				});
				return map;
			}else {
				return queryForObject(sql,Ioc.loadClass(s),args);
			}
		}
		return null;
	}
	default <T>Set<T> queryForSet(String sql,Class<T> clazz,Object...args){
		return new HashSet<>(queryForList(sql, clazz, args));
	}
	
	default <T>T queryForObject(String sql,Class<T> clazz,Object...args){
		try(var conn=getConnection();
				var stmt=conn.prepareStatement(sql)){
				if(args!=null) {
					for (int i = 0; i < args.length; i++) {
						stmt.setObject(i+1, args[i]);
					}
				}
				long s1=System.currentTimeMillis();
				var rs=stmt.executeQuery();
				if(Jdbc.showsql)
					log.info("queryForObject sql:{} {}ms",stmt.toString().substring(stmt.getClass().getName().length()),(System.currentTimeMillis()-s1));
				if (rs.next()){
					if(clazz.isArray()) {
						try (rs){
			    			var meta=rs.getMetaData();
			    			var os=new Object[meta.getColumnCount()];
			    			for (int i = 1; i <= meta.getColumnCount(); i++) {
			    				os[i]=rs.getObject(i);
			    			}
			    			return (T)os;
			    		} catch (SQLException e) {
			    			e.printStackTrace();
			    		}
					}else if(Map.class.isAssignableFrom(clazz)) {
						return (T)new JsonObject(rs);
					}else {
						return (T)rs.getObject(1);
					}
				}
			}catch (Exception e) {
				throw new RuntimeException(e);
			}
		return null;
	}
	
	default Map<String, Object> queryForMap(String sql,Object...args) {
		return queryForObject(sql,JsonObject.class,args);
	}
	default <T>Stream<T> queryForStream(String sql,Function<ResultSet,T> f,Object...args){
		RsSpliterator<T> spliterator = new RsSpliterator<>(sql,f,args);
        return StreamSupport.stream(spliterator,true).onClose(spliterator.close());
	}
	default <T>Stream<T> queryForStream(String sql,Class<T> clazz,Object...args){
		return queryForStream(sql,(rs)->{
			try {
				if(Map.class.isAssignableFrom(clazz)) {
					return (T)new JsonObject(rs);
				}else {
					return (T)rs.getObject(1);
				}
			} catch (Exception e) {
				return null;
			}
		},args).filter(r->r!=null);
	}
		
	default boolean execute(String sql){
		try(var conn=getConnection();
				var stam=conn.createStatement()){
			return stam.execute(sql);
		}catch(SQLException e) {
			throw new RuntimeException(e);
		}
	}
	default Object[] call(String sql,Object...args){
		try(var conn=getConnection();
				var stam=conn.prepareCall(sql)){
			if(args!=null)
				for (int i = 0; i < args.length; i++) {
					stam.setObject(i+1, args[i]);
				}
			boolean isrs=stam.execute();
			if(isrs) {
				var rs=stam.getResultSet();
				var count=rs.getMetaData().getColumnCount();
				var os=new Object[count];
				for (int i = 0; i < count; i++) {
					os[i]=rs.getObject(i+1);
				}
				return os;
			}
			return null;
		}catch(SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	default Map<String,Object> insert(Map<String,Object> map){
		StringBuilder sql=new StringBuilder("INSERT INTO ");
		sql.append(map.remove("table")).append("(");
		StringBuilder vals=new StringBuilder(" VALUES(");
		var id=map.remove("+id");
		var args=new Object[map.size()];
		int i=0;
		for (var k : map.keySet()) {
			sql.append("`").append(k).append("`,");
			vals.append("?,");
			args[i]=(map.get(k));
			i++;
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		vals.deleteCharAt(vals.length()-1);
		vals.append(")");
		var _id="id";
		var conn=getConnection();
		PreparedStatement stmt=null;
		try{
			long s1=System.currentTimeMillis();
			if(id!=null) {
				stmt=conn.prepareStatement(sql.append(vals).toString(),Statement.RETURN_GENERATED_KEYS);
				if(id instanceof String s) {
					_id=s;
				}
				for (int j = 0; j < args.length; j++) {
					if(args[j] instanceof Map||args[j] instanceof List ) {
						stmt.setString(j+1,Json.toJson(args[j]));
					}else
					stmt.setObject(j+1,args[j]);
				}
				stmt.executeUpdate();
				var rs=stmt.getGeneratedKeys();
				if (rs.next()) {
					map.put(_id,rs.getInt(1));
				}
				Jdbc.close(rs);
			}else {
				stmt=conn.prepareStatement(sql.append(vals).toString());
				for (int j = 0; j < args.length; j++) {
					if(args[j] instanceof Map||args[j] instanceof List ) {
						stmt.setString(j+1,Json.toJson(args[j]));
					}else
					stmt.setObject(j+1,args[j]);
				}
				stmt.executeUpdate();
			}
			if(Jdbc.showsql)
				log.info("insert sql:{} {}ms",stmt.toString().substring(stmt.getClass().getName().length()),(System.currentTimeMillis()-s1));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			Jdbc.close(stmt);
			Jdbc.close(conn);
		}
		return map;
	}
	default int[] insert(List<Map<String,Object>> list){
		var conn=getConnection();
		PreparedStatement pstm=null;
		try(conn){
			var mp0=list.get(0);
			var table=mp0.remove("table").toString();
			StringBuilder sb=new StringBuilder("insert into ")
					.append(table).append("(");
			mp0.forEach((k,v)->{
				sb.append(k).append(",");
			});
			sb.deleteCharAt(sb.length()-1).append(") values(");
			sb.append("?,".repeat(mp0.size()));
			sb.deleteCharAt(sb.length()-1).append(")");
			pstm=conn.prepareStatement(sb.toString());
			for (int i = 0; i < list.size(); i++) {
				var map=list.get(i);
				map.remove("table");
				int j=1;
				for (var v : map.values()) {
					pstm.setObject(j,v);
					j++;
				}
				pstm.addBatch();
			}
			return pstm.executeBatch();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if(pstm!=null)
				try {
					pstm.close();
					pstm=null;
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}
	
	default Map<String,Object> mager(Map<String,Object> map){
		var id=map.remove("id");
		if(map.containsKey("+id")||id==null||"".equals(id)) {//新增
			return insert(map);
		}
		var table=map.remove("table").toString();
		return update(table,map,Cnd.where("id","=",id));
	}
	default Map<String,Object> update(Map<String,Object> map){
		var table=map.remove("table").toString();
		return update(table,map,Cnd.where("id","=",map.remove("id")));
	}
	default Map<String,Object> update(String table,Map<String,Object> map,Object cnd){
		var args=new ArrayList<>();
		var sb=new StringBuilder("update ").append(table).append(" set ");
		map.forEach((k,v)->{
			sb.append("`").append(k).append("` = ?,");
			if(v instanceof Collection||v instanceof Map) {
				args.add(Json.toJson(v));
			}else {
				args.add(v);
			}
		});
		sb.deleteCharAt(sb.length()-1);
		if(cnd instanceof String s) {
			sb.append(s);
		}else if(cnd instanceof Cnd c) {
			sb.append(c);
			if(c.getParams().size()>0)
			args.addAll(c.getParams());
		}
		update(sb.toString(),args.toArray());
		return map;
	}
	
	default boolean delete(String sql,Object cnd){
		if(!sql.trim().toLowerCase().startsWith("delete ")) {
			sql="delete from "+sql;//sql 是表名
		}
		if(cnd instanceof Cnd c) {
			return update(sql+c,c.getParams().toArray())>0;
		}
		return update(sql,cnd)>0;
	}
	
	default int update(String sql,Object...args){
		try(var conn=getConnection();
				var stmt=conn.prepareStatement(sql)){
				if(args!=null) {
					for (int i = 0; i < args.length; i++) {
						if(args[i] instanceof String s) {
							stmt.setString(i+1,s);
						}else if(args[i] instanceof Number s) {
							stmt.setInt(i+1,s.intValue());
						}else if(args[i] instanceof Map||args[i] instanceof List ) {
							stmt.setString(i+1,Json.toJson(args[i]));
						}else {
							stmt.setObject(i+1, args[i]);
						}
					}
				}
				long s1=System.currentTimeMillis();
				int res=stmt.executeUpdate();
				if(Jdbc.showsql)
				log.info("update sql:{} {}ms",stmt.toString().substring(stmt.getClass().getName().length()),(System.currentTimeMillis()-s1));
				return res;
			}catch (SQLException e) {
				throw new RuntimeException(e);
			}
	}
	
	default long count(String sql,Object...args) {
		if(!sql.trim().toLowerCase().startsWith("select ")) {
			sql="select count(*) from "+sql;//sql 是表名
		}
		if(args!=null&&args[0] instanceof Cnd c) {
			return queryForObject(sql+c.toString(),long.class,c.getParams().toArray());
		}
		return queryForObject(sql,long.class,args);
	}
	
	default List<JsonObject> query(String sql,Object...args) {
		if(!sql.trim().toLowerCase().startsWith("select ")) {
			sql="select * from "+sql;//sql 是表名
		}
		return queryForList(sql,JsonObject.class,args);
	}

	default Pager pager(String sql, Object cnd,int num,int size) {
		return pager(sql,cnd,new Pager(num,size));
	};
	default Pager pager(String sql, Object cnd, Pager pager) {
		String countsql=pager.getSql();
		if(!sql.trim().toLowerCase().startsWith("select ")) {
			countsql="select IFNULL(count(*),0) from "+sql;
			sql="select * from "+sql;//sql 是表名
		}
		if(countsql==null) {
			int i=sql.trim().toLowerCase().indexOf(" from ");
			if(i!=-1) {
				countsql="select IFNULL(count(*),0)"+sql.substring(i);
			}else {
				countsql=sql.replace(" * "," count(*) ");
			}
			
		}
		List<JsonObject> data;
		if(cnd instanceof Cnd c) {
			data=queryForList(sql+cnd+pager.limit(), JsonObject.class,c.getParams().toArray());
			if(data==null)return null;
			countsql+=c.toString();
			int odby=countsql.indexOf("order by");
			if(odby!=-1) {
				countsql=countsql.substring(0, odby);
			}
			pager.setTotal(queryForObject(countsql,long.class,c.getParams().toArray()));
		}else {
			data=queryForList(sql+cnd+pager.limit(), JsonObject.class);
			if(data==null)return null;
			pager.setTotal(queryForObject(countsql+cnd,long.class));
		}
		pager.setData(data);
		return pager;
	};

	default List<Map> query(String sql,Object cnd){
		if(!sql.trim().toLowerCase().startsWith("select ")) {
			sql="select * from "+sql;//sql 是表名
		}
		if(cnd instanceof Cnd c) {
			if(c.getParams().size()>0) {
				return queryForList(sql+c,Map.class,c.getParams().toArray());
			}
		}
		return queryForList(sql+Objects.requireNonNullElse(cnd,""),Map.class);
	}
	default List<Map> query(String table, Object cnd,int num,int size){
		if(!table.trim().toLowerCase().startsWith("select ")) {
			table="select * from "+table;//sql 是表名
		}
		if(cnd instanceof Cnd c) {
			return queryForList(table+cnd+new Pager(num,size).limit(),Map.class,c.getParams().toArray());
		}
		return queryForList(table+cnd+new Pager(num,size).limit(),Map.class);
	}
	
	default List<Map> query(String table, Object cnd,int num,int size,String countsql){
		return queryForList(table+cnd+new Pager(num,size,countsql).limit(),Map.class);
	}
	
	default Map<?, ?> fetch(String table, Object cnd){
		String sql=table;
		if(!table.toLowerCase().startsWith("select ")) {
			sql="select * from "+table;
		}
		Object[] args=null;
		if(cnd==null) {
		}else if(cnd instanceof Cnd c) {
			sql+=c;
			args=c.getParams().toArray();
		}else if(cnd instanceof Map m) {
			var c=Cnd.where(m);
			sql+=c;
			args=c.getParams().toArray();
		}else {
			sql+=cnd.toString();
		}
		return queryForObject(sql,Map.class,args);
	}

	default boolean exists(String table) {
		boolean flag = false;
		try(var conn=getConnection();){
			var meta = conn.getMetaData();
			var rs = meta.getTables(null, null, table,new String[]{"TABLE"});
			flag = rs.next();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	};
}
